SQL Server 2012 Query Performance Tuning by Grant Fritchey

Author:Grant Fritchey
Language: eng
Format: epub, pdf
ISBN: 9781430242031
Publisher: Apress

Binding

The parse tree generated by the parser is passed to the next part of the algebrizer for processing. The algebrizer now resolves all the names of the different objects, meaning the tables, the columns, and so on, that are being referenced in the T-SQL in a process called binding. It also identifies all the various data types being processed. It even checks for the location of aggregates (such as GROUP BY and MAX). The output of all these verifications and resolutions is a binary set of data called a query processor tree.

To see this part of the algebrizer in action, if the following batch query (--algebrizertest in the download) is submitted, then the first three statements before the error statement are executed, and the errant statement and the one after it are cancelled.

IF (SELECT OBJECT_ID('dbo.Test1')

) IS NOT NULL

DROP TABLE dbo.Test1 ;

GO

CREATE TABLE dbo.Test1 (c1 INT) ;

INSERT INTO dbo.Test1

VALUES (1) ;

SELECT 'Before Error',

c1

FROM dbo.Test1 AS t ;

SELECT 'error',

c1

FROM dbo.no_Test1 ;

--Error: Table doesn't exist

SELECT 'after error' c1

FROM dbo.Test1 AS t ;

If a query contains an implicit data conversion, then the normalization process adds an appropriate step to the query tree. The process also performs some syntax-based optimization. For example, if the following query (--syntaxoptimize in the download) is submitted, then the syntax-based optimization transforms the syntax of the query, as shown in the T-SQL at the top of Figure 9-2, where BETWEEN becomes >= and <=.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.